﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.Data.Sql;
using System.Data.SqlClient;
using ruku_import.Model;


namespace ruku_import
{
    public partial class _Default : System.Web.UI.Page
    {
        List<产品库存> records = new List<产品库存>();

        /// <summary>
        /// 转换XLS文件到【LIST<表名>】
        /// </summary>
        /// <param name="filename">待转换的XLS文件名</param>
        private void ConvertToDataTable(string filename)
        {

            HSSFWorkbook hssfworkbook;
            using (FileStream fs = new FileStream(filename, FileMode.Open, FileAccess.Read))
            {
                hssfworkbook = new HSSFWorkbook(fs);
            }

            HSSFSheet sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();

            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;
                产品库存 one_record = new 产品库存();
                for (int i = 0; i < row.LastCellNum; i++)
                {
                    HSSFCell cell = row.GetCell(i);

                    if (cell == null)
                    {
                    }
                    else
                    {

                        var content = cell.ToString();
                        int iii=0;
                        byte bbb;
                        float fff;
                        bool success;
                        DateTime dd = new DateTime();

                        //首先看是第几列
                        switch (i)
                        {
                            //第一列则转化为相应的供货商ID
                            case 0:
                                //对供应商ID做INNER JOIN
                                switch (content)
                                {
                                    case "天津无缝":
                                        iii=1;
                                        break;

                                }
                                one_record.备注 = content;
                                break;
                            //第二列为入库单序号，转换为STR
                            case 1:
                                one_record.入库单ID = 1;
                                break;
                            //第三列为生产日期，转换为日期
                            case 2:
                                success = DateTime.TryParse(content, out dd);
                                if (success)
                                {
                                    one_record.生产日期 = dd;
                                    one_record.状态 = 1;
                                }
                                else
                                {
                                    one_record.生产日期 = null;
                                }
                                break;
                            //第四列为炉号，不管
                            case 3:
                                one_record.炉号 = content;
                                break;
                            //第5列为支数，转为INT
                            case 4:
                                byte.TryParse(content, out bbb);
                                one_record.支数 = bbb;
                                break;
                            //捆数编号
                            case 5:
                                one_record.捆号 = content;
                                break;
                            //重量（kg）
                            case 6:
                                float.TryParse(content, out fff);
                                one_record.重量 = fff;
                                break;
                            //长度
                            case 7:
                                float.TryParse(content, out fff);
                                one_record.长度 = fff;
                                break;
                            //入库日期
                            case 8:
                                success = DateTime.TryParse(content, out dd);
                                if (success)
                                {
                                    one_record.入库日期 = dd;
                                    one_record.状态 = 1;
                                }
                                else
                                {
                                    one_record.生产日期 = null;
                                }
                                break;
                            //存放区域不管
                            case 9:
                                success=byte.TryParse(content, out bbb);
                                if (success)
                                {
                                    one_record.库区 = bbb;
                                }
                                else {
                                    //如果为延安库区则不管
                                    one_record.状态 = null;
                                }
                                break;
                            //出库日期
                            case 10:
                                success = DateTime.TryParse(content, out dd);
                                if (success)
                                {
                                    one_record.出库日期 = dd;
                                    one_record.状态 = 2;
                                }
                                else
                                {

                                    one_record.出库日期 = null;
                                }
                                break;
                            //发运形式
                            case 11:

                                break;
                            //出库单号
                            case 12:

                                break;
                            //默认不做任何处理
                            default:

                                break;
                        }
                    }
                }
                //如果为合法记录则开始加工后加入LIST
                if (one_record.状态 !=null)
                {
                    one_record.产品类别ID = 1;
                    records.Add(one_record);
                }

            }
        }

        //主函数开始
        protected void Page_Load(object sender, EventArgs e)
        {
            ConvertToDataTable(Server.MapPath("12月油管入出库台帐.xls"));

            using (产品库存DataContext db = new 产品库存DataContext())
            {
                var supplys = db.原料供货单位.ToList();
                string bz="";
                //加工一下备注栏，让每一捆都有备注信息
                records.ForEach(delegate(产品库存 i) {
                    if (i.备注 != "")
                    {
                        bz = i.备注;
                    }
                    else
                    {
                        i.备注 = bz;
                    }                
                });
                //JOIN 原料供应商
                var res = (from p in records
                          join supply in supplys on p.备注 equals supply.名称
                          where p.状态==1
                          select new { 
                              ID=p.ID,
                              入库单ID=p.入库单ID,
                              出库单ID=p.出库单ID,
                              产品类别ID=p.产品类别ID,
                              原料供货ID=supply.ID,
                              生产单位ID=p.生产单位ID,
                              捆号=p.捆号,
                              炉号=p.炉号,
                              重量=p.重量,
                              长度=p.长度,
                              支数=p.支数,
                              生产日期=p.生产日期,
                              入库日期=p.入库日期,
                              出库日期=p.出库日期,
                              库区=p.库区,
                              状态=p.状态,
                              备注=p.备注
                        }).ToList();


                GridView1.DataSource = res;
                GridView1.DataBind();
            }

        }

    }
}



